﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;

namespace BHXH.Frm.Frm
{
    public partial class frmNhapDuLieuExcel : Form
    {

        ArrayList listTenCot = new ArrayList(); //list luu ten cac truong can lay
        ArrayList output = new ArrayList();
        string mangcachang;
        Excel.Application myApp = new Excel.Application();
        public frmNhapDuLieuExcel()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Title = "Mở file excell!";
            openFile.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
            if (openFile.ShowDialog() == DialogResult.OK)
            {
                txtTenFile.Text = openFile.FileName;
            }            
        }

        public void getData(ArrayList cot) //ham lay ve cac hang theo yeu cau
        {

            Excel.Application myApp = new Excel.Application();

            string FileName = this.txtTenFile.Text.ToString();

            Excel.Workbook workbook = myApp.Workbooks.Open(FileName,
                 Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing);

            Excel.Worksheet sheet = workbook.Sheets[2];
            Excel.Range range = sheet.UsedRange;
            int columns = range.Columns.Count;
            int rows_count = range.Rows.Count;
            double test = sheet.Cells[2, 1].value;


            if (test != 1.0)
            {
                MessageBox.Show("Bạn đã không nhập đúng các cột theo yêu cầu");
            }
            else
            {
                for (int i = 2, j = 1; j <= columns; j++)
                {
                    foreach (int tencotcanlay in cot) //tencotcanlay trong mang nhap vao
                    {
                        if (tencotcanlay == sheet.Cells[i, j].value)
                        {
                            for (int hangcanlay = (i + 1), cotcanlay = j; hangcanlay <= rows_count; hangcanlay++)
                            {
                                mangcachang += sheet.Cells[hangcanlay, cotcanlay].value + "  ";
                            }
                            MessageBox.Show("Hang:" + mangcachang);
                        }
                    }
                    mangcachang = null;
                }

            }
            workbook.Close(false);

        }

        private void frmNhapDuLieuExcel_Load(object sender, EventArgs e)
        {
            listTenCot.Add(1);
            listTenCot.Add(3);
        }
        
    }
}
